﻿CREATE PROCEDURE GenListaInchidere    @strDataEnd varchar(50), @strContStart AS varchar(50)  AS

DECLARE @dataUI as float, @dtDataStartUI as datetime,@dtDataEndUI as datetime
DECLARE @strContEnd as TipCont
DECLARE @strDataStart as varchar(50)
DECLARE @tabela varchar(100)
DECLARE @categorie as tinyint
DECLARE @strcategorie as varchar(10)
DECLARE @analitica bit
DECLARE @cfm bit
DECLARE @dataStartFunctie varchar(50)
DECLARE @dataEndFunctie varchar(50)
DECLARE @lunaCurenta smallint
DECLARE @ultimaData datetime

--determina ultima data la care s-a lucrat
SELECT @ultimaData= MAX(DataDoc) FROM RulajeDoc


SET @categorie = 0
SET @strcategorie = '0'
SET @analitica = 1
SET @cfm = 1
SET @lunaCurenta=1

SET @strDataEnd = LTRIM(@strDataEnd)
SET @strDataEnd = RTRIM(@strDataEnd)

IF LEN(@strDataEnd)<>0
BEGIN
	SET @dtDataEndUI = CAST(CAST(@strDataEnd AS float)-2 AS datetime)
	SET @strDataEnd = ''''+CAST(@dtDataEndUI AS varchar(50))+''''
	SET @dataEndFunctie = ''''+CAST(YEAR(@dtDataEndUI) as varchar(4))+'-'+CAST(MONTH(@dtDataEndUI) AS varchar(2))+'-'+CAST(DAY(@dtDataEndUI) AS varchar(2))+''''
	SET @lunaCurenta = MONTH(@dtDataEndUI)	
	SET @dataStartFunctie = ''''+CAST(YEAR(@dtDataEndUI) as varchar(4))+'-'+CAST(MONTH(@dtDataEndUI) AS varchar(2))+'-01'''
END
ELSE
BEGIN
	SET @dataEndFunctie = ''''+CAST(@ultimaData AS varchar(50))+''''
	SET @dataStartFunctie = ''''+CAST(YEAR(@ultimaData) as varchar(4))+'-'+CAST(MONTH(@ultimaData) AS varchar(2))+'-01'''
	SET @lunaCurenta = MONTH(@ultimaData)	
END

IF  EXISTS(SELECT * FROM tempdb..SysObjects WHERE name = '#balanta' AND type = 'U')	
BEGIN
	DROP TABLE  #balanta
	IF @@ERROR != 0 GOTO EROARE				
END

CREATE TABLE [#balanta_t] (
	[Cont] varchar(50) NOT NULL, 
	[Denumire] varchar(200) NOT NULL DEFAULT '',
	[Tip] varchar(3) NOT NULL DEFAULT '',
	[ContParinte] varchar(50) NOT NULL, 
	[Terminal] bit NOT NULL,	

	[DebInit] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CreInit] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[DebPrec] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CrePrec] DECIMAL(20,2) NOT NULL DEFAULT 0,

	[DebPrecCalc] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CrePrecCalc] DECIMAL(20,2) NOT NULL DEFAULT 0,


	[DebCurent] DECIMAL(20,2) NOT NULL DEFAULT 0,
	[CreCurent] DECIMAL(20,2) NOT NULL DEFAULT 0,
)

DECLARE @strSelect	varchar(8000)
DECLARE @filtData varchar(500)
DECLARE @filtDataC varchar(500)
DECLARE @filtC varchar(500)
SET @filtData = ''
SET @filtDataC = ''
SET @filtC = ''


IF LEN(@strContStart) <>0
	SET @filtC =  '  AND F.ContDebit ='''+@strContStart + ''' '

SET @filtData = @filtData + @filtC
SET @filtDataC = REPLACE(@filtData,'F.ContDebit','F.ContCredit') 
SET @filtC = REPLACE(@filtC,'F.ContDebit','C.Cont') 



SET @strSelect =' INSERT INTO [#balanta_t]
	 SELECT c.Cont, C.Denumire,  C.Tip,  C.ContParinte,C.Terminal, c.DebitInitial AS [DebInit], c.CreditInitial AS [CreInit], 
	DebitPrecedent, CreditPrecedent,sum(COALESCE(DebPrecCalc, 0)) DebPrecCalc,sum(COALESCE(CrePrecCalc, 0)) CrePrecCalc,  
	sum(COALESCE(DebCurent, 0)) DebCurent, sum(COALESCE(CreCurent, 0)) CreCurent
	FROM [Conturi]  c
LEFT OUTER JOIN 
	(SELECT  
			COALESCE(t2.ContCredit, t1.ContDebit) Cont,
			COALESCE(t1.SumaDebitPrec , 0) DebPrecCalc, 
			COALESCE(t2.SumaCreditPrec , 0) CrePrecCalc,  
			COALESCE(t1.SumaDebit , 0) DebCurent, 
			COALESCE(t2.SumaCredit , 0) CreCurent 
		FROM 
			(SELECT coalesce(SUM(dbo.InainteDeData(F.ValoareLei,cast('+@dataStartFunctie+' as datetime),D.DataDoc)),0) sumaDebitPrec,
			coalesce(SUM(dbo.IntreDatele(F.ValoareLei,cast('+@dataStartFunctie+' as datetime),cast('+@dataEndFunctie+' as datetime),D.DataDoc)),0) sumaDebit, F.ContDebit 
			FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE F.ContDebit<>''N/A''  '  + @filtData + 
	'GROUP BY F.ContDebit) t1 
		FULL OUTER JOIN 
			(SELECT coalesce(SUM(dbo.InainteDeData(F.ValoareLei,cast('+@dataStartFunctie+' as datetime),D.DataDoc)),0) sumaCreditPrec,
			coalesce(SUM(dbo.IntreDatele(F.ValoareLei,cast('+@dataStartFunctie+' as datetime), cast('+@dataEndFunctie+' as datetime),D.DataDoc)),0) sumaCredit, F.ContCredit
			FROM [RulajeDoc] D INNER JOIN [Rulaje] F ON D.[IDDocument] = F.[IDDocument] 
			WHERE F.ContCredit <>''N/A'' ' + @filtDataC + '
			GROUP BY F.ContCredit) t2 
		ON t1.ContDebit = t2.ContCredit 
	) RUL 
ON dbo.IsContInclus(c.cont,rul.cont)=1'

SET @strSelect = @strSelect + ' WHERE  c.[Cont]<>''N/A''  ' + @filtC+' AND Categorie='+@strcategorie
SET @strSelect = @strSelect + 'GROUP BY  c.Cont, C.Denumire,  C.Tip,  C.ContParinte,C.Terminal,c.CreditInitial, CreditPrecedent,  c.DebitInitial, DebitPrecedent'
--ON substring(rul.cont,1,len(c.cont))=c.cont


PRINT @strSelect
EXEC(@strSelect)
IF @@ERROR != 0 GOTO EROARE				

SELECT  Cont,ContParinte,Terminal,Denumire,Tip,DebInit,CreInit, DebPrec+DebPrecCalc DebPrec,CrePrec+CrePrecCalc CrePrec, DebCurent, CreCurent,
	dbo.SoldDebit(Tip, DebInit, CreInit) SoldInitDeb, 
	dbo.SoldCredit(Tip, DebInit, CreInit) SoldInitCre, 
	dbo.Sold(Tip, DebInit, CreInit) SoldInit, 
	dbo.SoldDebit(Tip, DebPrec+DebPrecCalc, CrePrec+CrePrecCalc) SoldPrecDeb, 
	dbo.SoldCredit(Tip, DebPrec+DebPrecCalc, CrePrec+CrePrecCalc) SoldPrecCre, 
	dbo.Sold(Tip, DebPrec, CrePrec) SoldPrec, 
	dbo.SoldDebit(Tip, DebCurent, CreCurent) SoldCurentDeb, 
	dbo.SoldCredit(Tip, DebCurent, CreCurent) SoldCurentCre, 
	dbo.Sold(Tip, DebCurent, CreCurent) SoldCurent,
	DebInit + DebPrec +DebPrecCalc+ COALESCE(DebCurent, 0) DebTotal, CreInit + CrePrec+CrePrecCalc + COALESCE(CreCurent, 0) CreTotal, 
	dbo.SoldDebit(Tip, DebInit + DebPrec+DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrec+CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotalDeb, 
	dbo.SoldCredit(Tip, DebInit + DebPrec+DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrec+CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotalCre,
	dbo.Sold(Tip, DebInit + DebPrec+DebPrecCalc + COALESCE(DebCurent, 0), CreInit + CrePrec+CrePrecCalc + COALESCE(CreCurent, 0)) SoldTotal
INTO [#balanta] FROM [#balanta_t] 	

SELECT Cont,ContParinte,Denumire,Terminal,Tip, DebPrec,CrePrec, DebCurent,CreCurent, DebPrec+DebCurent as [RulDebTotal], CrePrec+CreCurent as [RulCreTotal],SoldTotalDeb,SoldTotalCre FROM [#balanta] 


GOTO Gata
EROARE:
	RAISERROR ('Eroare la generarea balansei',16,-1)
Gata: